﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
/*
	Todo:
	3. investigate help on rc submenus

	1. Refactor the table key parsing. 
	2. neaten the key list processing.
	4. Still like the idea of integrity check
	5. Note: existing foreign key scripting is not schema compliant.
		- need to extract all the information from the row into the command parameter.
*/
namespace SqlCeDriver
{
	public partial class ForeignKeyForm : Form
	{
		public ForeignKeyForm(DbViewDriver.IDataSource dataSource, String fkeyTable, String fkeyField)
		{
			InitializeComponent();
			this.dataSource = dataSource;
			this.fkeyTable = fkeyTable;
			this.fkeyField = fkeyField;
		}

		private void ForeignKeyForm_Load(object sender, EventArgs e)
		{
			SetControls();
		}

		private void SetControls()
		{
			String tf = String.Format("{0}.{1}", this.fkeyTable, this.fkeyField);
			lblFK.Text = tf;
			PopulateKeyList();
			SetMatchString("");
			ListBoxToText(listPkFields, lblPK);
			comboOnDelete.Items.Add("No Action");
			comboOnDelete.Items.Add("Cascade");
			comboOnDelete.Items.Add("Set Null");
			comboOnDelete.SelectedIndex = 0;
			comboOnUpdate.Items.Add("No Action");
			comboOnUpdate.Items.Add("Cascade");
			comboOnUpdate.Items.Add("Set Null");
			comboOnUpdate.SelectedIndex = 0;
		}

		private void PopulateKeyList()
		{
			SqlCeDataSource dataSource = this.dataSource as SqlCeDataSource;
			// 
			String sql = String.Format(PKFields, this.fkeyTable, this.fkeyField);
			DataTable dt = new Helper(dataSource.GetAccessor()).GenericQuery(dataSource.ConnectionString, sql, "PKINFO");
			// remove multiple pkey tables
			foreach (DataRow dr in dt.Rows)
			{
				int keySequence = (int)dr[2]; // 3rd column is index field column count
				if (keySequence > 1)			// ie. a multiple primary key
				{
					String pkeyTable, pkeyField;
					ForeignKey.ParseTableField(dr[0].ToString(), out pkeyTable, out pkeyField);
					foreach (DataRow dr2 in dt.Rows)
					{
						String pkeyTableTest;
						ForeignKey.ParseTableField(dr2[0].ToString(), out pkeyTableTest, out pkeyField);
						if (pkeyTableTest == pkeyTable)
							dr2[2] = keySequence; // just flag all keys for exclusion as >1
					}
				}
			}
			// 
			foreach (DataRow dr in dt.Rows)
			{
				// include if type matches and not a multiple key.
				if ((int)dr[1] == 1 && (int)dr[2] <= 1)
					this.primaryKeys.Add(dr[0].ToString());
			}
		}

		private void ListPkFields_SelectedIndexChanged(object sender, EventArgs e)
		{
			ListBoxToText(listPkFields, lblPK);
		}

		private void ListBoxToText(ListBox lb, Control tb)
		{
			String targetText = "";
			if (lb.SelectedIndex >= 0)
				targetText = lb.Items[lb.SelectedIndex].ToString();
			tb.Text = targetText;
		}

		private void BtnClose_Click(object sender, EventArgs e)
		{
			this.DialogResult = System.Windows.Forms.DialogResult.Cancel;
			this.Close();
		}
		
		private void BtnRestore_Click(object sender, EventArgs e)
		{
			SetMatchString("");
		}
		
		private void BtnScript_Click(object sender, EventArgs e)
		{
			String script;
			if (TryMakeScript(out script))
			{
				// show it somehow
				ShowSourceForm dialog = new ShowSourceForm("Foreign key script", script, "");
				dialog.ShowDialog(this);
			}
		}
		
		private void BtnCreate_Click(object sender, EventArgs e)
		{
			String script;
			if (TryMakeScript(out script))
			{
				bool result;
				SqlCeDataSource dataSource = this.dataSource as SqlCeDataSource;
				String execMessage = new Helper(dataSource.GetAccessor()).GenericExecute(this.dataSource.ConnectionString, script, out result);

				if (!result)
					ShowError(this, execMessage);
				else
				{
					ShowError(this, "Foreign Key created OK");
					this.DialogResult = System.Windows.Forms.DialogResult.OK;
					Close();
				}
			}
		}

		bool TryMakeScript(out String script)
		{
			script = "";

			// don't forget schemas
			String primaryKey = lblPK.Text;
			String foreignKey = lblFK.Text;
			if (primaryKey == foreignKey)
			{
				ShowError(this, String.Format("You can't create a foreign key from the same field"));
				listPkFields.Focus();
				return false;
			}
			// split the 
			String pkeyTable, pkeyField;
			String fkeyTable, fkeyField;
			if (!ForeignKey.ParseTableField(primaryKey, out pkeyTable, out pkeyField))
			{
				ShowError(this, String.Format("Error in PK format {0}. Expected was Table.Field", primaryKey));
				listPkFields.Focus();
				return false;
			}
			// Hmm. the FK was sent in. We can't fix this!
			if (!ForeignKey.ParseTableField(foreignKey, out fkeyTable, out fkeyField))
			{
				ShowError(this, String.Format("Error in FK format {0}. Expected was Table.Field", foreignKey));
				listPkFields.Focus();
				return false;
			}

			UpdateOption onDelete = MakeAction(this.comboOnDelete.SelectedItem.ToString());
			UpdateOption onUpdate = MakeAction(this.comboOnUpdate.SelectedItem.ToString());

			script = MakeFkScript(fkeyTable, fkeyField, pkeyTable, pkeyField, onDelete, onUpdate);
			return true;
		}

		private UpdateOption MakeAction(string text)
		{
			if (text.ToUpper().IndexOf("CASC") != -1) return UpdateOption.Cascade;
			if (text.ToUpper().IndexOf("NULL") != -1) return UpdateOption.SetToNull;
			return UpdateOption.NoAction;
		}

		private string MakeFkScript(string fkeyTable, string fkeyField, string pkeyTable, string pkeyField, UpdateOption onDelete, UpdateOption onUpdate)
		{
			ForeignKey fkey = new ForeignKey(fkeyTable, fkeyField, pkeyTable, pkeyField);
			// SQLCE does not support if statements.
//            String FKSCRIPT_TEMPLATE = @"
//alter table {1} add constraint {0} FOREIGN KEY ({2}) references {3} ({4})
//";
            const String FKSCRIPT_TEMPLATE = @"
-- add without assuming integrity
alter table {1}
	add constraint {0} FOREIGN KEY ({2}) 
	references {3} ({4})
	on delete {6} 
	on update {7}
";
			
			String fkeyName = String.Format("{0}_{1}_{2}",fkey.FkTable(false).Replace(".", "_"), fkey.FkField(false), 
														fkey.PkTable(false).Replace(".", "_"));
			String schemaPrefix = (fkey.FkSchema.Length == 0) ? "" : fkey.FkSchema + ".";

			String delete = (onDelete == UpdateOption.Cascade) ? "cascade" :
							(onDelete == UpdateOption.SetToNull) ? "set null" : "no action";
			String update = (onUpdate == UpdateOption.Cascade) ? "cascade" :
							(onUpdate == UpdateOption.SetToNull) ? "set null" : "no action";

			return String.Format(FKSCRIPT_TEMPLATE, fkey.GenerateFkName(), 
													fkey.FkTable(true),fkey.FkField(true), 
													fkey.PkTable(true),fkey.PkField(true), schemaPrefix,
													delete, update);
		}

		//private bool ParseTableField(string inputString, out string table, out string field)
		//{
		//    table = field = "";
		//    int iPos1 = inputString.IndexOf('.');
		//    if (iPos1 == -1) return false; // no .
		//    // allow for schema tables in which case schema.table.field
		//    int iPos2 = inputString.IndexOf('.', ++iPos1);
		//    if (iPos2 != -1) iPos1 = iPos2 + 1;
		//    table = inputString.Substring(0, iPos1-1);
		//    field = inputString.Substring(iPos1);
		//    return true;
		////}

		private void ShowError(ForeignKeyForm parent, string message)
		{
			MessageBox.Show(parent, message, "Input error",MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
		}

		// deal with noise chars
		static private String MakeSearchText(String source)
		{
			return source.Replace("_", "").Replace("-", "").Replace(" ", "").ToUpper();
		}

		private void SetMatchString(String newMatch)
		{
			if (newMatch.CompareTo(this.primaryKeyFilterString) == 0)
				return; // do nothing if nothing to do.
			this.primaryKeyFilterString = MakeSearchText(newMatch);
			ApplyFilter(this.primaryKeyFilterString);
			btnRestore.Enabled = (this.primaryKeyFilterString.Length > 0) ? true : false;
		}

		private bool Matches(String target, String pattern)
		{
			if (pattern.Length == 0) // no search set
				return true;
			// simple contains
			if (MakeSearchText(target).IndexOf(pattern) >= 0)
				return true;
			return false;
		}

		void ApplyFilter(String filter)
		{
			// hold current selection. no why bother
			listPkFields.Items.Clear();
			if (filter.Length > 0)
				listPkFields.ForeColor = System.Drawing.Color.Red;
			else
				listPkFields.ForeColor = System.Drawing.SystemColors.WindowText;

			foreach (String pk in this.primaryKeys)
			{
				if (Matches(pk, filter))
					listPkFields.Items.Add(pk);
			}
			if (listPkFields.Items.Count > 0)
				listPkFields.SelectedIndex = 0;
		}

		private void ListFields_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
		{
			char c = e.KeyChar;
			if ((c >= '0' && c <= '9')
				|| (c >= 'A' && c <= 'Z')
				|| (c >= 'a' && c <= 'z')
				|| (c == '_' || c == '_'))
			{
				SetMatchString(this.primaryKeyFilterString + c);
				e.Handled = true;
			}
			else if (c == 8)
			{
				int l = this.primaryKeyFilterString.Length;
				if (l > 0)
				{
					SetMatchString(this.primaryKeyFilterString.Substring(0, l - 1));
					e.Handled = true;
				}
			}
			else if (c == 27)
			{
				SetMatchString("");
				e.Handled = true;
			}
		}
		
		DbViewDriver.IDataSource dataSource;
		String fkeyTable;
		String fkeyField;
		enum UpdateOption {NoAction, Cascade, SetToNull}

		List<String> primaryKeys = new List<string>();
		String primaryKeyFilterString;
		
		const String PKFields = @"
select TABLE_NAME+'.'+COLUMN_NAME as PKFIELD,
	   1 as TypeMatch,
	   ORDINAL_POSITION as index_column_id
from INFORMATION_SCHEMA.INDEXES 
where PRIMARY_KEY = 1
";
	}
}
